package com.jk.Util;

import com.jk.pojo.PayLog;
import com.jk.pojo.PayShopsCoupon;
import com.jk.pojo.PayUserCoupon;

/**
 * 动态sql拼接
 * Created by Administrator on 2017/12/22 0022.
 */
public class DynamicSql {
    /**
     * 调用sql工具类中的对应查询方法进行查询用户共有多少张可用的优惠券
     * @param userid 用户id
     * @return
     */
    public String queryPayUserCouponCount(String userid){
        StringBuffer sql = new StringBuffer("select count(*) from pay_user_coupon where 1=1 ");
        if(userid != null){
            sql.append(" and userId=#{userid}");
        }
        sql.append(" and status = 1");
        return sql.toString();
    }


    /**
     * 根据用户id查询出用户可用优惠券的总条数
     * @param userid 用户id
     * @return
     */
    public String queryPayShopsCouponCount(String userid){
        StringBuffer sql = new StringBuffer("SELECT count(*) FROM " +
                "pay_user_coupon");
        sql.append(" where 1 = 1");
        if(userid != null){
            sql.append(" AND userId = #{userid}");
        }
        sql.append(" and status = 1");
        return sql.toString();
    }

    /**
     *根据用户id查询出用户可用优惠券的具体信息
     * @param userid 用户id
     * @param start 开始条数
     * @param rows 每页条数
     * @return
     */
    public String queryPayShopsCouponList(String userid,int start,int rows){
        StringBuffer sql = new StringBuffer("SELECT * FROM pay_user_coupon t1,pay_shops_coupon t2,pay_shops t3 WHERE 1 = 1 ");
        sql.append(" AND t1.couponId = t2.couponId AND t2.shopId = t3.shopId ");
        if(userid != null){
            sql.append(" AND t1.userId = #{userid}");
        }
        sql.append(" AND t1.status = 1 limit #{start},#{rows}");
        return sql.toString();
    }

    /**
     *根据优惠券id查询具体的优惠券信息
     * @param couponid 优惠券id
     * @return
     */
    public String queryPayShopsCoupon(String couponid){
        StringBuffer sql = new StringBuffer("SELECT * from pay_shops_coupon where 1=1 ");
        if(couponid != null){
            sql.append(" and couponId = #{couponid}");
        }
        return sql.toString();
    }


    /**
     * 根据优惠券id查询用户是否拥有该优惠券
     * @param couponid 优惠券id
     * @return
     */
    public String queryPayUserCouponCountByCouponId(String couponid){
        StringBuffer sql = new StringBuffer("SELECT count(*) from pay_user_coupon where 1=1 ");
        if(couponid != null){
            sql.append(" and couponId = #{couponid}");
        }
        return sql.toString();
    }


    /**
     *将用户领取的优惠券添加到用户优惠券表中
     * @param payUserCoupon 用户优惠券
     * @return
     */
    public String insertPayUserCoupon(PayUserCoupon payUserCoupon){
        StringBuffer sql = new StringBuffer("insert into pay_user_coupon values(#{ucid},#{userid},#{couponid},#{overduetime},#{status})");
        return sql.toString();
    }

    /**
     *查询出用户优惠券的主键id和过期时间
     * @param
     * @return
     */
    public String queryPayUserCouponOverdueTime(){
        StringBuffer sql = new StringBuffer("select ucid,overdueTime from pay_user_coupon");
        return sql.toString();
    }


    /**
     * 根据优惠券id删除优惠券
     * @param ucid
     * @return
     */
    public String deletePayUserCouponStart(String ucid){
        StringBuffer sql = new StringBuffer("delete from pay_user_coupon where 1 = 1 ");
        if(ucid != null){
            sql.append(" AND ucId = #{ucid}");
        }
        return sql.toString();
    }

    /**
     *根据优惠券id删除商家过期的优惠券
     * @param couponid
     * @return
     */
    public String deletePayShopsCouponStart(String couponid){
        StringBuffer sql = new StringBuffer("delete from pay_shops_coupon where 1 = 1");
        if(couponid != null){
            sql.append(" AND couponId = #{couponid}");
        }
        return sql.toString();
    }


    /**
     *查询商家优惠券主键id和过期时间
     *  @param
     * @return
     */
    public String queryPayShopsCouponStartOverdueTime(){
        StringBuffer sql = new StringBuffer("select couponId,overdueTime from pay_shops_coupon");
        return sql.toString();
    }

    /**
     * 根据用户id修改用户余额
     * @param userid
     * @return
     */
    public String updateUserBalance(Double goodCommodityPrice,String userid){
        StringBuffer sql = new StringBuffer("update pay_user set userBalance = userBalance - #{goodCommodityPrice} where 1 = 1 ");
        if(userid != null){
            sql.append(" AND userId = #{userid}");
        }
        return sql.toString();
    }


    /**
     * 根据用户优惠券主键id修改此优惠券状态为已使用
     * @param ucid
     * @return
     */
    public String updateUserCouponStauts(String ucid){
        StringBuffer sql = new StringBuffer("update pay_user_coupon set status = 2  where 1 = 1 ");
        if(ucid != null){
            sql.append(" AND ucId = #{ucid}");
        }
        return sql.toString();
    }

    /**
     *根据用户id查询出用户余额
     * @param userid
     * @return
     */
    public String queryUserUserBalance(String userid){
        StringBuffer sql = new StringBuffer("select userBalance,userAccount,userId,paymentPwd from pay_user where 1 = 1");
        if(userid != null){
            sql.append(" AND userId = #{userid}");
        }
        return sql.toString();
    }


    /**
     * 根据用户id修改用户余额
     * @param integral
     * @param userid
     * @return
     */
    public String updateUserIntegral(int integral,String userid){
        StringBuffer sql = new StringBuffer("update pay_user set userIntegral = userIntegral + #{integral} where 1 = 1");
        if(userid != null){
            sql.append(" AND userId = #{userid}");
        }
        return sql.toString();
    }

    /**
     * 用户领取优惠券以后将商家优惠券领取数量加一
     * @param couponid
     * @return
     */
    public String updatePayShopsCouponGetCount(String couponid){
        StringBuffer sql = new StringBuffer("update pay_shops_coupon set getCount = getCount + 1 where 1 = 1");
        if(couponid != null){
            sql.append(" AND couponId = #{couponid}");
        }
        return sql.toString();
    }

    /**
     * 查询用户优惠券状态
     * @param ucid
     * @return
     */
    public String queryPayUserCouponStatus(String ucid){
        StringBuffer sql = new StringBuffer("select status from pay_user_coupon where 1 = 1");
        if(ucid != null){
            sql.append(" AND  ucId = #{ucid}");
        }
        return sql.toString();
    }


    /**
     * 新增消费记录信息
     * @param log
     * @return
     */
    public String insertPayUserCouponLog(PayLog log){
        StringBuffer sql = new StringBuffer(" insert into pay_log(logId,logType,logInfo,saveTime,dealAmont,logSuccess,runNumber,userId,operatorId , logStatus )"+
                " values ( #{logId},#{logType},#{logInfo},#{saveTime},#{dealAmont},#{logSuccess},#{runNumber},"+
                " #{userId},#{operatorId},#{logStatus} ) ");
        return sql.toString();
    }

    /**
     * 添加商家优惠券
     * @param payShopsCoupon
     * @return
     */
    public String insertPayShopsCoupon(PayShopsCoupon payShopsCoupon){
        StringBuffer sql = new StringBuffer(" insert into pay_shops_coupon(couponId,couponMoney,shopId," +
                "couponType,overdueTime,couponInfo,provideCount,couponName,fullReduceMoney)"+
                " values ( #{couponid},#{couponmoney},#{shopid},#{coupontype},#{overduetime},#{couponinfo},"+
                " #{providecount},#{couponname},#{fullreducemoney} ) ");
        return sql.toString();
    }


    /**
     * 查询商家优惠券
     * @return
     */
    public String queryShopsCouponListAll(){
        StringBuffer sql = new StringBuffer("select * from pay_shops_coupon");
        return sql.toString();
    }
}
